package cl.mt.data;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collection;
import java.util.LinkedList;

import cl.mt.dto.DTOPais;
import cl.mt.dto.filtros.PaisRegioCiudadDTO;
import cl.mt.util.bd.DatosTabla;
import cl.mt.util.bd.Parametro;

public class DataPais extends DatosTabla {
	private static final String sqlGetId = "SELECT MAX(ID)+1 AS IDX FROM MASTRANSPORTE.MST_PAISES";
	private static final String sqlFindByPK = "SELECT ID, DESCRIPCION FROM MASTRANSPORTE.MST_PAISES where ID=?";
	private static final String sqlFindAll = "SELECT ID, DESCRIPCION FROM MASTRANSPORTE.MST_PAISES";
	private static final String sqlCreate = "INSERT INTO MASTRANSPORTE.MST_PAISES (ID, DESCRIPCION) VALUES (?, ?)";
	private static final String sqlDeleteByPK = "DELETE FROM MASTRANSPORTE.MST_PAISES where ID=?";
	private static final String sqlUpdate = "UPDATE MASTRANSPORTE.MST_PAISES SET DESCRIPCION=? where ID=?";
	private static final String sqlFindCiudadFiltro = "SELECT CIU.DESCRIPCION as CIUDAD, CIU.ID as CIUDAD_ID, REG.DESCRIPCION AS REGION , REG.ID AS REGION_ID , PAI.DESCRIPCION AS PAISES,  PAI.ID AS PAIS_ID FROM MASTRANSPORTE.MST_CIUDADES CIU,  MASTRANSPORTE.MST_REGIONES REG, MASTRANSPORTE.MST_PAISES PAI where CIU.RGN_ID= REG.ID AND REG.PAI_ID=PAI.ID AND CIU.ID=?";


	private DTOPais setDatosRegistro(ResultSet rs) throws SQLException {
		DTOPais dto = new DTOPais();
		dto.setId(formatInteger(rs.getObject("ID")));
		dto.setDescripcion(formatString(rs.getObject("DESCRIPCION")));
		return dto;
	}

	public Integer generateId() {
		Parametro[] params = null;
		Integer id = null;
		try {
			getConnection();
			ResultSet rs = executeQuery(sqlGetId, params);
			while (rs.next()) {
				id = formatInteger(rs.getObject("IDX"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		return id;
	}
	public DTOPais findByPrimaryKey(Long id) {

		DTOPais dto = new DTOPais();
		Parametro[] params = new Parametro[] { getParametro(id, Types.INTEGER)};
		try {
			getConnection();

			ResultSet rs = executeQuery(sqlFindByPK, params);
			while (rs.next()) {
				dto = setDatosRegistro( rs );
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return dto;
	}

	public Collection<DTOPais> findAll() {

		Parametro[] params = null;
		Collection<DTOPais> col = new LinkedList<DTOPais>();
		try {
			getConnection();

			ResultSet rs = executeQuery(sqlFindAll, params);
			while (rs.next()) {
				DTOPais dto = setDatosRegistro( rs );
				col.add( dto );
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return col;
	}

	public int deleteByPrimaryKey(Integer id) {

		Parametro[] params = new Parametro[] { getParametro(id, Types.INTEGER)};
		int res = -1;
		try {
			getConnection();
			res = executeUpdate(sqlDeleteByPK, params);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return res;
	}

	public int create(DTOPais dto) {

		Parametro[] params = new Parametro[] {
			getParametro(dto.getId(), Types.INTEGER),
			getParametro(dto.getDescripcion(), Types.VARCHAR)
		};

		int res = -1;
		try {
			getConnection();
			res = executeUpdate(sqlCreate, params);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return res;
	}

	public int update(DTOPais dto) {

		Parametro[] params = new Parametro[] {
			getParametro(dto.getDescripcion(), Types.VARCHAR),
			getParametro(dto.getId(), Types.INTEGER)
		};

		int res = -1;
		try {
			getConnection();
			res = executeUpdate(sqlUpdate, params);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return res;
	}

	public PaisRegioCiudadDTO findFindCiudadFiltro(Integer idCiudad) {

		PaisRegioCiudadDTO dto = new PaisRegioCiudadDTO();
		Parametro[] params = new Parametro[] { getParametro(idCiudad, Types.INTEGER)};
		try {
			getConnection();

			ResultSet rs = executeQuery(sqlFindCiudadFiltro, params);
			while (rs.next()) {
				// CIUDAD      CIUDAD_ID     REGION      REGION_ID     PAIS     PAIS_ID      
				dto.setIdCiudad(formatInteger(rs.getObject("CIUDAD_ID")));
				dto.setCiudad(formatString(rs.getObject("CIUDAD")));
			
				dto.setIdRegion(formatInteger(rs.getObject("REGION_ID")));
				dto.setRegion(formatString(rs.getObject("REGION")));
				
				dto.setIdPais(formatInteger(rs.getObject("PAIS_ID")));
				dto.setPais(rs.getString("PAISES"));
				
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return dto;
	}
}

